﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;

namespace project
{
    using SystemData = System.Data;
    public partial class Form1 : Form
    {
        SystemData.DataTable table;

        private string importFileName = "";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
           ImportXML();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ExportToExcel(table);
        }

        private void PrintValues(SystemData.DataTable table, string label)
        {
            string str = "";
            str += label + "\n";
            foreach (SystemData.DataRow row in table.Rows)
            {
                foreach (SystemData.DataColumn column in table.Columns)
                {
                    str += row[column] + "\t";
                }
            }
            MessageBox.Show(str);
        }

        /// <summary>
        /// 导入场景配置文件
        /// </summary>
        private void ImportXML()
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "XML|*.xml";
            openFile.Title = "导入场景配置xml文件";
            if (openFile.ShowDialog() == DialogResult.OK
             && !string.Empty.Equals(openFile.FileName))
            {
                importFileName = openFile.FileName;
               
                table = new SystemData.DataTable();
                table.ReadXml(importFileName);
                //PrintValues(newTable, openFile.FileName);
                MessageBox.Show("导入成功!");
            }
        }

        /// <summary> 
        /// 导出Excel 
        /// </summary> 
        /// <param name="dt">要导出的DataTable</param> 
        private void ExportToExcel(System.Data.DataTable dt)
        {
            if (dt == null)
            {
                MessageBox.Show("先导入场景XML!");
                return;
            }

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                return;
            }
            System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
            saveDia.Filter = "Excel|*.xls";
            saveDia.Title = "导出为Excel文件";
            string[] pathArr = importFileName.Split(new string[] { "\\", "." }, StringSplitOptions.RemoveEmptyEntries);
            saveDia.FileName = pathArr[pathArr.Length - 2];
            if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK
             && !string.Empty.Equals(saveDia.FileName))
            {
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
                worksheet.Name = pathArr[pathArr.Length - 2];
                Microsoft.Office.Interop.Excel.Range range = null;
                long totalCount = dt.Rows.Count;
                long rowRead = 0;
                float percent = 0;
                string fileName = saveDia.FileName;
                //写入注释
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName.IndexOf("ID") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "对象ID";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("Type") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "类型";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("IdIndex") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "索引";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("NeedInit") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "初始化需要显示";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("PrefabName") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "预制体名";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("CommonFile") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "文件名";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ColliderScaleX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "碰撞体大小X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ColliderScaleY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "碰撞体大小Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ShadowScaleX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "阴影大小X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ShadowScaleY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "阴影大小Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ScaleX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "大小X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ScaleY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "大小Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ShadowPosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "阴影位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ShadowPosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "阴影位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ColliderPosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "碰撞体位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ColliderPosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "碰撞体位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("NamePosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "姓名位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("NamePosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "姓名位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("TaskStatusPosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "任务图标位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("TaskStatusPosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "任务图标位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("AniPosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "模型动画的位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("AniPosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "模型动画的位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("PosX") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "位置X";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("PosY") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "位置Y";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("FrameSpeed") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "模型动画帧速";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("Speed") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "走动的速度";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("ReapeatTime") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "走动的频率";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("Name") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "姓名";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("WalkRadius") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "走动的半径";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("CommonScaleZ") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "大小Z";
                    }
                    else if (dt.Columns[i].ColumnName.IndexOf("CommonPosZ") != -1)
                    {
                        worksheet.Cells[1, i + 1] = "位置Z";
                    }
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    //range.Interior.ColorIndex = 15;//背景颜色 
                    range.Font.Bold = true;//粗体 
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中 
                    //加边框 
                    range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                }

                //写入标题 
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                    //range.Interior.ColorIndex = 15;//背景颜色 
                    range.Font.Bold = true;//粗体 
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中 
                    //加边框 
                    range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                    //range.ColumnWidth = 4.63;//设置列宽 
                    //range.EntireColumn.AutoFit();//自动调整列宽 
                    //r1.EntireRow.AutoFit();//自动调整行高 
                }
                //写入内容 
                for (int r = 0; r < dt.DefaultView.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 3, i + 1] = dt.DefaultView[r][i];
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1];
                        range.Font.Size = 9;//字体大小 
                        //加边框 
                        range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.EntireColumn.AutoFit();//自动调整列宽 
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                    System.Windows.Forms.Application.DoEvents();
                }
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                if (dt.Columns.Count > 1)
                {
                    range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                }
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(fileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                    return;
                }

                workbooks.Close();
                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    int generation = System.GC.GetGeneration(xlApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                    System.GC.Collect(generation);
                }
                GC.Collect();//强行销毁 
                #region 强行杀死最近打开的Excel进程
                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killId = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killId = m;
                    }
                }
                if (excelProc[killId].HasExited == false)
                {
                    excelProc[killId].Kill();
                }
                #endregion
                MessageBox.Show("导出成功!");
            }
        }

        


        /// <summary>
        /// For Test
        /// </summary>
        private SystemData.DataTable CreateTestTable(string tableName)
        {
            // Create a test DataTable with two columns and a few rows.
            table = new SystemData.DataTable(tableName);
            SystemData.DataColumn column = new SystemData.DataColumn("id", typeof(System.Int32));
            column.AutoIncrement = true;
            table.Columns.Add(column);

            column = new SystemData.DataColumn("item", typeof(System.String));
            table.Columns.Add(column);


            // Add ten rows.
            SystemData.DataRow row;
            for (int i = 0; i <= 9; i++)
            {
                row = table.NewRow();
                row["item"] = "item " + i;
                table.Rows.Add(row);
            }

            table.AcceptChanges();
            return table;
        }


    }
}